﻿namespace SupermarketSystem.Client
{
    using System;
    using System.Data.Entity;
    using System.Data.OleDb;
    using System.Data.SQLite;
    using System.Globalization;
    using System.IO;
    using System.IO.Compression;
    using System.Linq;
    using System.Text;
    using System.Threading;
    using System.Xml;

    using MongoDB.Data;
    using MongoDB.Driver;
    using MongoDB.Driver.Linq;

    using iTextSharp.text;
    using iTextSharp.text.pdf;

    using SupermarketSystem.MSSQLData;
    using SupermarketSystem.MSSQLData.Migrations;
    using SupermarketSystem.MSSQLModels;
    using SupermarketSystem.MySQLModels;
    using SupermarketSystem.SQLiteModels;

    public class Client
    {
        public static void Main()
        {
            Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.InvariantCulture;
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<SupermarketSystemContext, Configuration>());

            using (var superMarketContext = new SupermarketSystemContext())
            {
                //    // Force Initialization
                superMarketContext.Database.Initialize(true);

                //    // Migrate SQL Database
                //MigrateSQLDatabase(superMarketContext);
            }

            // Migrate Excel ZIP
            //MigrateExcelZip(@"..\..\..\..\ExcelData\Sample-Sales-Reports.zip");

            // Generate Agregated Report
            //GenerateAggregatedSalesReportPDF(@"..\..\..\..\PDFReports\AgregatedReport.pdf");

            // Generate XML Report
            //GenerateAggregatedSaleXMLReport(@"..\..\..\..\XMLReports\Sales-by-Vendors-report.xml");

            // Migrate SQL To MongoDB
            //FromSqlToMongo();

            // Generate JSON Reports
            //FromMongoToJSON(@"..\..\..\..\Product-Reports\");

            // Generate Expenses From XML To SQL
            //GenerateVendorExpensesFromXML(@"..\..\..\..\XMLData\Vendors-Expenses.xml");

            // Generate Expenses From XML To Mongo
            //GenerateExpensesFromXmlToMongo();

            // Fill SQLite
            //MigrateSQLiteFromMongo();
        }

        private static void MigrateSQLiteFromMongo()
        {
            using (var context = new SQLiteEntities())
            {
                var products = LoadData<MongoProduct>().ToList();

                foreach (var product in products)
                {
                    var p = new SQLiteModels.Products()
                    {
                        ProductName = product.ProductName,
                        VendorName = product.VendorName,
                        TotalQuantitySold = product.TotalQuantitySold,
                        TotalIncomes = product.TotalIncomes
                    };

                    context.Products.Add(p);
                }

                context.SaveChanges();

                var expenses = LoadData<MongoVendorExpenses>().ToList();

                foreach (var expense in expenses)
                {
                    var e = new SQLiteModels.Expens()
                    {
                        VendorID = expense.VendorId,
                        VendorName = (string)expense.VendorName,
                        ExpenseSum  = expense.ExpenseSum,
                        Month = (DateTime)expense.Month
                    };

                    context.Expenses.Add(e);
                }

                context.SaveChanges();
            }
        }

        private static void MigrateSQLDatabase(SupermarketSystemContext superMarketContext)
        {
            using (var mySQLModels = new MySQLModels())
            {
                foreach (var vendor in mySQLModels.Vendors)
                {
                    superMarketContext.Vendors.Add(new MSSQLModels.Vendor
                    {
                        VendorName = vendor.Name
                    });
                }
                superMarketContext.SaveChanges();

                foreach (var measure in mySQLModels.Measures)
                {
                    superMarketContext.Measures.Add(new MSSQLModels.Measure
                    {
                        MeasureName = measure.Name
                    });
                }
                superMarketContext.SaveChanges();

                foreach (var product in mySQLModels.Products)
                {
                    superMarketContext.Products.Add(new MSSQLModels.Product
                    {
                        ProductName = product.Name,
                        BasePrice = product.BasePrice,
                        MeasureID = product.MeasuresId,
                        VendorID = product.VendorsId
                    });
                }
                superMarketContext.SaveChanges();
            }
        }

        private static string GetConnectionString(string filePath)
        {
            try
            {
                var sbConnection = new OleDbConnectionStringBuilder();
                var strExtendedProperties = string.Empty;
                sbConnection.DataSource = filePath;

                if (Path.GetExtension(filePath).Equals(".xls")) // for 97-03 Excel file
                {
                    sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
                    strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1"; // HDR = ColumnHeader, IMEX = InterMixed
                }
                else if (Path.GetExtension(filePath).Equals(".xlsx")) // for 2007 Excel file
                {
                    sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                    strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
                }

                sbConnection.Add("Extended Properties", strExtendedProperties);

                return sbConnection.ToString();
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static string GetTemporaryDirectory()
        {
            string tempDir = Path.Combine(
                Path.GetTempPath(),
                Path.GetRandomFileName());

            Directory.CreateDirectory(tempDir);

            return tempDir;
        }

        public static void MigrateExcelZip(string zipPath)
        {
            string extractPath = GetTemporaryDirectory();

            using (ZipArchive archive = ZipFile.OpenRead(zipPath))
            {
                archive.ExtractToDirectory(extractPath);
            }

            RecursiveFileProcessor(extractPath);
        }

        public static void RecursiveFileProcessor(string path)
        {
            if (Directory.Exists(path))
            {
                ProcessDirectory(path);
            }
            else
            {
                Console.WriteLine("{0} is not a valid file or directory.", path);
            }
        }

        public static void ProcessDirectory(string dir)
        {
            string[] fileEntries = Directory.GetFiles(dir);
            foreach (string fileName in fileEntries)
            {
                ProcessFile(fileName);
            }

            // Recurse into subdirectories
            string[] subdirectoryEntries = Directory.GetDirectories(dir);
            foreach (string subdirectory in subdirectoryEntries)
            {
                ProcessDirectory(subdirectory);
            }
        }

        public static void ProcessFile(string path)
        {
            DirectoryInfo dir = new DirectoryInfo(path);

            var reportDate = DateTime.ParseExact(
                dir.Parent.ToString(),
                "dd-MMM-yyyy",
                CultureInfo.InvariantCulture);


            var dbConnString = GetConnectionString(path);
            var excelConnection = new OleDbConnection(dbConnString);
            excelConnection.Open();

            using (var superMarketContext = new SupermarketSystemContext())
            using (excelConnection)
            {
                var command = new OleDbCommand("SELECT * FROM [Sales$];", excelConnection);

                var reader = command.ExecuteReader();
                var firstRow = true;

                using (reader)
                {
                    Report report = new Report();

                    while (reader.Read())
                    {
                        // Iterate once
                        if (firstRow)
                        {
                            var storeName = reader[0] as string;

                            var store = superMarketContext.Stores
                                .FirstOrDefault(x => x.StoreName == storeName);

                            // If store does not exist
                            if (store == null)
                            {
                                // Add new store
                                store = new Store
                                {
                                    StoreName = storeName
                                };

                                superMarketContext.Stores.Add(store);
                                superMarketContext.SaveChanges();
                            }

                            // Add report
                            report = new Report
                            {
                                ReportDate = reportDate,
                                StoreID = store.StoreID
                            };

                            superMarketContext.Reports.Add(report);
                            superMarketContext.SaveChanges();

                            firstRow = false;
                        }

                        var firstCol = (string)reader[0];
                        int number;

                        // If first column is useful data
                        if (int.TryParse(firstCol, out number))
                        {
                            var productIDString = (string)reader[0];
                            var quantityString = (string)reader[1];
                            var unitPriceString = ((string)reader[2]).Replace(
                                ",",
                                CultureInfo.InvariantCulture.NumberFormat.NumberDecimalSeparator);
                            var sumString = ((string)reader[3]).Replace(
                                ",",
                                CultureInfo.InvariantCulture.NumberFormat.NumberDecimalSeparator);


                            var productID = int.Parse(productIDString);
                            var quantity = int.Parse(quantityString);
                            var unitPrice = decimal.Parse(unitPriceString);
                            var sum = decimal.Parse(sumString);

                            var entry = new ReportEntry
                            {
                                ReportID = report.ReportID,
                                ProductID = productID,
                                Quantity = quantity,
                                UnitPrice = unitPrice,
                                Sum = sum
                            };

                            superMarketContext.ReportEntries.Add(entry);
                        }
                    }

                    // Save all entries
                    superMarketContext.SaveChanges();
                }
            }
        }

        private static void GenerateAggregatedSalesReportPDF(string path)
        {
            const int grayColor = 200;
            var normalFont = FontFactory.GetFont(FontFactory.HELVETICA, 10);
            var boldFont = FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10);

            using (var superMarketContext = new SupermarketSystemContext())
            {
                var reportsByDate =
                    from r in superMarketContext.Reports
                    group r by r.ReportDate into g
                    select new { Date = g.Key, Reports = g };

                var doc = new Document();
                PdfWriter.GetInstance(doc, new FileStream(path, FileMode.Create));

                doc.Open();

                var table = new PdfPTable(5);
                float[] widths = new float[] { 1f, 1f, 1f, 3f, 1f  };
                table.SetWidths(widths);

                var header = new PdfPCell(
                    new Phrase("Aggregated Sales Report", boldFont));
                header.Colspan = 5;
                header.FixedHeight = 25f;
                header.HorizontalAlignment = 1; // 0 == left; 1 == center; 2 == right;
                table.AddCell(header);

                foreach (var date in reportsByDate.ToList())
                {
                    var datTimeAsString = string.Format(
                        "{0:dd-MMM-yyyy}",
                        date.Date);

                    var dateCell = new PdfPCell(
                        new Phrase("Date: " + datTimeAsString, normalFont));
                    dateCell.Colspan = 5;
                    dateCell.BackgroundColor = new GrayColor(grayColor);
                    table.AddCell(dateCell);

                    var productCell = new PdfPCell(
                        new Phrase("Product", boldFont));
                    productCell.BackgroundColor = new GrayColor(grayColor);
                    table.AddCell(productCell);

                    var quantityCell = new PdfPCell(
                        new Phrase("Quantity", boldFont));
                    quantityCell.BackgroundColor = new GrayColor(grayColor);
                    table.AddCell(quantityCell);

                    var unitPriceCell = new PdfPCell(
                        new Phrase("Unit Price", boldFont));
                    unitPriceCell.BackgroundColor = new GrayColor(grayColor);
                    table.AddCell(unitPriceCell);

                    var locationCell = new PdfPCell(
                        new Phrase("Location", boldFont));
                    locationCell.BackgroundColor = new GrayColor(grayColor);
                    table.AddCell(locationCell);

                    var sumCell = new PdfPCell(
                        new Phrase("Sum", boldFont));
                    sumCell.BackgroundColor = new GrayColor(grayColor);
                    table.AddCell(sumCell);

                    var sumTotal = 0m;

                    foreach (var report in date.Reports)
                    {
                        foreach (var entry in report.Entries)
                        {
                            table.AddCell(new Phrase(entry.Product.ProductName, normalFont));
                            table.AddCell(new Phrase(entry.Quantity.ToString() + " " + entry.Product.Measure.MeasureName, normalFont));
                            table.AddCell(new Phrase(entry.UnitPrice.ToString(), normalFont));
                            table.AddCell(new Phrase(report.Store.StoreName, normalFont));

                            var entrySumCell = new PdfPCell(
                                new Phrase(entry.Sum.ToString(), normalFont));
                            entrySumCell.HorizontalAlignment = 2;
                            table.AddCell(entrySumCell);

                            sumTotal += entry.Sum;
                        }
                    }

                    var dateFooterCell = new PdfPCell(
                        new Phrase("Total sum for " + datTimeAsString + ":", normalFont));
                    dateFooterCell.Colspan = 4;
                    dateFooterCell.HorizontalAlignment = 2;
                    table.AddCell(dateFooterCell);

                    var sumTotalCell = new PdfPCell(
                        new Phrase(sumTotal.ToString(), boldFont));
                    sumTotalCell.HorizontalAlignment = 2;
                    table.AddCell(sumTotalCell);
                }

                doc.Add(table);
                doc.Close();
            }
        }

        private static void GenerateAggregatedSaleXMLReport(string path)
        {
            Encoding encoding = Encoding.GetEncoding("utf-8");

            using (var superMarketContext = new SupermarketSystemContext())
            using (var writer = new XmlTextWriter(path, encoding))
            {
                writer.Formatting = Formatting.Indented;
                writer.IndentChar = '\t';
                writer.Indentation = 2;

                var entriesByVendor = superMarketContext
                    .ReportEntries
                    .GroupBy(x => new { x.Product.Vendor }, (key, group) => new
                    {
                        Vendor = key.Vendor,
                        Dates = group
                            .Where(x => x.Product.VendorID == key.Vendor.VendorID)
                            .GroupBy(y => new { y.Report.ReportDate }, (k, g) => new
                            {
                                Date = k.ReportDate,
                                Sum = g.Sum(s => s.Sum)
                            })
                    })
                    .OrderBy(x => x.Vendor.VendorName);


                // Open root element
                writer.WriteStartDocument();
                writer.WriteStartElement("sales");

                foreach (var e in entriesByVendor.ToList())
                {
                    // Open sale element
                    writer.WriteStartElement("sale");
                    writer.WriteAttributeString("vendor", e.Vendor.VendorName);

                    foreach (var d in e.Dates)
                    {
                        // Add summary element
                        writer.WriteStartElement("summary");
                        writer.WriteAttributeString("date", d.Date.ToString("dd-MMM-yyyy"));
                        writer.WriteAttributeString("total-sum", d.Sum.ToString());
                        writer.WriteEndElement();
                    }

                    // Close sale element
                    writer.WriteEndElement();
                }

                // Close root
                writer.WriteEndElement();
            }
        }

        public static void FromSqlToMongo()
        {
            using (var superMarketContext = new SupermarketSystemContext())
            {
                var productIDs = superMarketContext.ReportEntries.
                    Select(x => x.ProductID).Distinct();

                foreach (var id in productIDs)
                {
                    var productName = superMarketContext.Products.Find(id).ProductName;
                    var vendorName = superMarketContext.Products.Find(id).Vendor.VendorName;
                    var quantity = superMarketContext.ReportEntries.
                        Where(x => x.ProductID == id).Sum(x => x.Quantity);
                    var incomes = superMarketContext.ReportEntries.
                        Where(x => x.ProductID == id).Sum(x => x.Sum);

                    MongoProduct product = new MongoProduct()
                    {
                        ProductId = id,
                        ProductName = productName,
                        VendorName = vendorName,
                        TotalQuantitySold = quantity,
                        TotalIncomes = incomes
                    };

                    SaveData<MongoProduct>(product);
                }
            }
        }

        public static void FromMongoToJSON(string path)
        {
            var products = LoadData<MongoProduct>().ToList();

            foreach (var product in products)
            {
                StreamWriter writer = new StreamWriter(path + product.ProductId.ToString() + ".json");
                using (writer)
                {
                    StringBuilder sb = new StringBuilder();

                    sb.Append("{").AppendLine();
                    sb.AppendFormat("\tproduct-id: {0},", product.ProductId).AppendLine();
                    sb.AppendFormat("\tproduct-name: {0},", product.ProductName).AppendLine();
                    sb.AppendFormat("\tvendor-name: {0},", product.VendorName).AppendLine();
                    sb.AppendFormat("\ttotal-quantity-sold: {0},", product.TotalQuantitySold).AppendLine();
                    sb.AppendFormat("\ttotal-incomes: {0},", product.TotalIncomes).AppendLine();
                    sb.Append("}").AppendLine();

                    writer.WriteLine(sb);
                }
            }
        }

        private static void SaveData<T>(T product)
        {
            try
            {
                MongoDBProvider.db.GetCollection<T>(typeof(T).Name).Save(product, SafeMode.True);
            }
            catch (MongoConnectionException)
            {
                throw new MongoCommandException("Cannot access database. Please try again later");
            }
        }

        private static IQueryable<T> LoadData<T>()
        {
            try
            {
                return MongoDBProvider.db.GetCollection<T>(typeof(T).Name).AsQueryable();
            }
            catch (MongoConnectionException ex)
            {
                throw new MongoCommandException("Cannot access database. Please try again later " + ex.Message);
            }
        }

        private static void GenerateVendorExpensesFromXML(string path)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(path);
            XmlNodeList vends = doc.SelectNodes("//sale");

            SupermarketSystemContext supercont = new SupermarketSystemContext();

            foreach (XmlNode v in vends)
            {
                XmlNodeList expenses = v.SelectNodes("expenses");

                var nameString = v.Attributes["vendor"].Value.ToString();

                var vNameList = (from vend in supercont.Vendors
                                 where vend.VendorName == nameString
                                 select vend.VendorID).ToList();

                int vendID = -1;

                if (vNameList.FirstOrDefault() != 0)
                {
                    vendID = vNameList[0];
                }
                else
                {
                    MSSQLModels.Vendor newVend = new MSSQLModels.Vendor { VendorName = v.Attributes["vendor"].Value.ToString() };
                    supercont.Vendors.Add(newVend);
                    supercont.SaveChanges();
                    vendID = newVend.VendorID;
                }


                foreach (XmlNode expense in expenses)
                {
                    string monthYear = expense.Attributes["month"].Value;

                    var exMonth = DateTime.ParseExact(monthYear
                       , "MMM-yyyy",
                        CultureInfo.InvariantCulture);

                    var exSum = decimal.Parse(expense.InnerText);

                    VendorExpense vexpense = new VendorExpense
                    {
                        VendorID = vendID,
                        Month = exMonth,
                        ExpenseSum = exSum

                    };
                    supercont.VendorExpenses.Add(vexpense);
                    supercont.SaveChanges();
                }

            }
        }

        private static void GenerateExpensesFromXmlToMongo()
        {
            using (var superMarketContext = new SupermarketSystemContext())
            {
                var vendorExpenses = superMarketContext.VendorExpenses;
                foreach (var vex in vendorExpenses)
                {
                    var vendorID = vex.VendorExpenseID;
                    var vendorName = superMarketContext.Vendors.
                    Find(vendorID).VendorName;
                    var month = vex.Month;
                    var expenseSum = vex.ExpenseSum;
                    MongoVendorExpenses vendorExpense = new MongoVendorExpenses()
                    {
                        VendorId = vendorID,
                        VendorName = vendorName,
                        Month = month,
                        ExpenseSum = expenseSum
                    };
                    SaveData<MongoVendorExpenses>(vendorExpense);
                }
            }
        }
    }
}
